package electronet.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;

import electronet.modelo.Persona;
import electronet.excepcion.DAOExcepcion;
import electronet.util.ConexionBD;

public class personaDAO {
 
public Collection<Persona> listar() throws DAOExcepcion {
Collection<Persona> c = new ArrayList<Persona>();
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
con = ConexionBD.obtenerConexion();
String query = "select idpersona,idtipopersona,nombres,paterno,materno,sexo,tipodocumento,nrodocumento,"
+ "correo,celular,fechanacimiento from persona "
+ "where idpersona<>0 order by Nombres&paterno&materno";
stmt = con.prepareStatement(query);
rs = stmt.executeQuery();
while (rs.next()) {
Persona vo = new Persona();
vo.setIdtipopersona(rs.getInt("idtipopersona"));
vo.setNombres(rs.getString("nombres"));
vo.setPaterno(rs.getString("paterno"));
vo.setMaterno(rs.getString("materno"));
vo.setSexo(rs.getString("sexo"));
vo.setIdpersona(rs.getInt("idpersona"));
c.add(vo);
}

} catch (SQLException e) {
System.err.println(e.getMessage());
throw new DAOExcepcion(e.getMessage());
} 
 
 
return c;
}
 
public void ExistePersona(){
 
 
}
 
public Persona insertar(Persona per) throws DAOExcepcion {
String query = "insert into persona(idtipopersona,Nombres,Paterno,Materno,Sexo,"
+ "TipoDocumento,NroDocumento,Correo,Celular,FechaNacimiento,roles_idRol,Nickname,password) "
+ " values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
con = ConexionBD.obtenerConexion();
stmt = con.prepareStatement(query);
 
stmt.setInt(1, per.getIdtipopersona());
stmt.setString(2, per.getNombres());
stmt.setString(3, per.getPaterno());
stmt.setString(4, per.getMaterno());
stmt.setString(5, per.getSexo());
stmt.setString(6, per.getTipodocumento());
stmt.setString(7, per.getNrodocumento());
stmt.setString(8, per.getCorreo());
stmt.setString(9, per.getCelular());
//stmt.setDate(10,null);
stmt.setDate(10,per.getFechanacimiento());
stmt.setInt(11, per.getIdroles());
stmt.setString(12, per.getNickname());
stmt.setString(13, per.getPassword());
 
 
int i = stmt.executeUpdate();
if (i != 1) {
throw new SQLException("No se pudo insertar");
}
// Obtener el ultimo id
int id = 0;
query = "select last_insert_id()";
stmt = con.prepareStatement(query);
rs = stmt.executeQuery();
if (rs.next()) {
id = rs.getInt(1);
}
per.setIdpersona(id);

} catch (SQLException e) {
System.err.println(e.getMessage());
throw new DAOExcepcion(e.getMessage());

}
return per;
}

public Persona obtener(int idPersona) throws DAOExcepcion {
Persona per = new Persona();
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
String query = "idtipopersona,Nombres,Paterno,Materno,Sexo,"
+ "TipoDocumento,NroDocumento,Correo,Celular,FechaNacimiento,roles_idRol "
+ "from persona where idpresona=?";
con = ConexionBD.obtenerConexion();
stmt = con.prepareStatement(query);
 
rs = stmt.executeQuery();
if (rs.next()) {
per.setIdpersona(rs.getInt(1));
per.setNombres(rs.getString(2));
per.setPaterno(rs.getString(3));
per.setMaterno(rs.getString(4));
per.setSexo(rs.getString(5));
per.setTipodocumento(rs.getString(6));
per.setNrodocumento(rs.getString(7));
per.setCorreo(rs.getString(8));
per.setCelular(rs.getString(9));
per.setFechanacimiento(null);//(Date) rs.getDate(10));
per.setIdroles(rs.getInt(11));
per.setIdpersona(idPersona);
}
} catch (SQLException e) {
System.err.println(e.getMessage());
throw new DAOExcepcion(e.getMessage());
 
}
return per;
}

public void eliminar(int idPersona) throws DAOExcepcion {
String query = "delete from persona WHERE idpersona=?";
Connection con = null;
PreparedStatement stmt = null;
try {
con = ConexionBD.obtenerConexion();
stmt = con.prepareStatement(query);
stmt.setInt(1, idPersona);
int i = stmt.executeUpdate();
if (i != 1) {
throw new SQLException("No se pudo eliminar");
}
} catch (SQLException e) {
System.err.println(e.getMessage());
throw new DAOExcepcion(e.getMessage());
}
}

public Persona actualizar(Persona per) throws DAOExcepcion {
String query = "update persona set idtipoperona = ?, Nombres = ?, Paterno = ?, Materno = ?,Sexo=? TipoDocumento = ?,"
                +   "NroDocumento = ?, Correo = ?, Celular=?,FechaNacimiento = ?"
+ " where idpersona=?";
 
 
Connection con = null;
PreparedStatement stmt = null;
try {
con = ConexionBD.obtenerConexion();
stmt = con.prepareStatement(query);
 
stmt.setInt(1, per.getIdtipopersona());
stmt.setString(2, per.getNombres());
 
stmt.setString(3, per.getPaterno());
stmt.setString(4, per.getMaterno());
stmt.setString(5, per.getSexo());
stmt.setString(6, per.getTipodocumento());
stmt.setString(7, per.getNrodocumento());
stmt.setString(8, per.getCorreo());
stmt.setString(9, per.getCelular());
stmt.setDate(10,null);//(Date) per.getFechanacimiento());(Date) per.getFechanacimiento());
stmt.setInt(10, per.getIdpersona());
 
int i = stmt.executeUpdate();
if (i != 1) {
throw new SQLException("No se pudo actualizar");
}
} catch (SQLException e) {
System.err.println(e.getMessage());
throw new DAOExcepcion(e.getMessage());
} finally {

}
return per;
}
 

}